import flask
import sqlite3
import json

app=flask.Flask(__name__)
class StudentDB:
    def openDB(self):
        self.con=sqlite3.connect("students.db")
        self.cursor=self.con.cursor()

    def closeDB(self):
        self.con.commit()
        self.con.close()
     def initTable(self):
            res={}
            try:
                self.cursor.execute("create table students (No varchar(16) primary key,Name varchar(16), Sex varchar(8), Age int)")
                res["msg"]="OK"
            except Exception as err:
                res["msg"]=str(err)
            return res

    def insertRow(self,No,Name,Sex,Age):
        res={}
        try:
            self.cursor.execute("insert into students (No,Name,Sex,Age) values (?,?,?,?)",(No,Name,Sex,Age))
            res["msg"]="OK"
        except Exception as err:
            res["msg"]=str(err)
        return res

 def deleteRow(self,No):
        res={}
        try:
            self.cursor.execute("delete from students where No=?",(No,))
            res["msg"]="OK"
        except Exception as err:
            res["msg"]=str(err)
        return res
 
    def selectRows(self):
        res={}
        try:
            data=[]
            self.cursor.execute("select * from students order by No")
            rows=self.cursor.fetchall()
            for row in rows:
                d={}
                d["No"]=row[0]
                d["Name"]=row[1]
                d["Sex"]=row[2]
                d["Age"]=row[3]
                data.append(d)
            res["msg"]="OK"
            res["data"]=data
        except Exception as err:
            res["msg"]=str(err)
        return res




